package com.sz.service;

import com.sz.pojo.RevExp;
import com.sz.service.RevExpSVImpl;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.nio.charset.StandardCharsets;
import java.util.List;

@Controller
public class DownloadSV {
    @Resource
    private RevExpSVImpl revExpSV;

    @RequestMapping("/download")
    public void downloadStatement(HttpServletResponse response,String adder,int type){
        //创建工作薄
        HSSFWorkbook statement_excel = new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet = statement_excel.createSheet("收支报表");
        // 表头
        HSSFRow headRow = sheet.createRow(0);
        //居中格式
        HSSFCellStyle style = statement_excel.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        HSSFCell cell = null;
        String headContent[] = {"添加者","金额","类型","日期","描述"};
        for (int i=0;i<5;i++){
            cell = headRow.createCell(i);
            cell.setCellValue(headContent[i]);
            cell.setCellStyle(style);
        }

        //添加报表数据
        String sql = "where 1=1 ";
        if (!adder.equals("all")){
            sql += "and adder = '" + adder +"'";
        }
        if (type!=2){
            sql += "and type = " + type;
        }
        List<RevExp> records = revExpSV.getByChoose(sql);
        for (int i=1;i<=records.size();i++){
            headRow = sheet.createRow(i);
            RevExp record = records.get(i-1);

            cell = headRow.createCell(0);
            cell.setCellValue(record.getAdder());
            cell.setCellStyle(style);

            cell = headRow.createCell(1);
            cell.setCellValue(record.getMoney());
            cell.setCellStyle(style);

            cell = headRow.createCell(2);
            cell.setCellValue(record.getType());
            cell.setCellStyle(style);

            cell = headRow.createCell(3);
            cell.setCellValue(record.getDate());
            cell.setCellStyle(style);

            cell = headRow.createCell(4);
            cell.setCellValue(record.getDescribes());
            cell.setCellStyle(style);
        }

        File file = new File("D:\\报表.xls");
        try {
            FileOutputStream fos = new FileOutputStream(file);
            statement_excel.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        }

        //网页端进行下载
        try {
            //准备将Excel的输出流通过response输出到页面下载
            //八进制输出流
            response.setContentType("application/octet-stream");
            //设置导出Excel的名称
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename="+new String("报表.xlsx".getBytes("utf-8"),"iso-8859-1"));
            //刷新缓冲
            response.flushBuffer();
            //将Excel写入到response的输出流中，供页面下载
            statement_excel.write(response.getOutputStream());
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
